Aggregate tool
Use the
Aggregate tool to allow records at a lower database level to be aggregated (rolled up) at a higher one. This functionality is typically used to rank customers and products according to criteria from lower or 'foreign' database tables.
For example, in a Household > Customer > Order database hierarchy you could create a Sum of orders at customer level using this tool. A number of other functions are available in addition to ‘Sum’.
If they are made permanent then aggregate columns will be visible and available in the Data Explorer just like any other column.
Procedure
- Drag the
Aggregate tool to the Workspace.
- Choose the join operator you want to use.
- Click the icon to the right of the
New Aggregate field. A new window opens containing the tool's fields.
- Enter a display name in the
Display Name field.
If you save the tool as a template, this name will be used to display it in the
Templates tab. The display name is also used in the Caption area if you insert the tool into a document.
- If you want the new column to be automatically indexed, select the
Index Column check box. You should only index columns when necessary. For example, when engineered columns have > 250 discrete values, indexing can start to improve query performance.
| note: |
The default value for the
Index Column check box is unselected. If you import a Campaign Manager document with the
Index Column check box selected, the check box will revert to unselected. This is because when you import an existing engineering node, you are effectively creating a new one and the check box default of unselected is applied. |
- Optional setting: if you want to make the column permanent so that it is saved in the database, and can be viewed and selected in the
Data Explorer tab, click the
Optional expand icon
 . In the
Table Column Name
field, enter the name you want to use to display the column in the
Data Explorer tab.
- In the Settingssection:
- Aggregate Function: Select the function you want to use to perform the aggregate. If you select
Count, you will need to configure a source table, whereas all the other functions will require you to select a source column.
- Target Table Level: Select the resolution level of the aggregate, that is the table on which the new column will be created if it is made permanent. You must configure this field before you can select a source table or column.
- Source Table / Column: Depending on the selected aggregate function and Target Table level, choose the table or column that the aggregate will be derived from. Only tables foreign to the selected Target table will be available to select from the drop down. If you select an Aggregate function other than 'Count' then this field will be 'Column Source' and you will need to select the column that the aggregate function will be performed on, for example 'Sum of Order Value'. Only columns that are appropriate for the selected function will be displayed, for example numeric columns for 'sum' or 'mean' aggregate functions, and so on.
- Filter Segment: Click into the
Filter field to open a pop-up in which a filter can be built using the same tools and join operators available to normal segments.
- Click
Process Document to make the column permanent (it is created on the target table).
You might need to refresh the Data Explorer to view your new column. |
|
